Skip Headers

Oracle® Database Application Developer's Guide - Object-Relational Features 10g Release 2 (10.2) Part Number B14260-01 |
Home | Book List | Contents | Index | Master Index | Feedback

Previous | Next
| View PDF

# A Sample Application Using Object-Relational Features

This appendix describes a sample application that provides an overview of how to create and use user-defined datatypes (Oracle Objects). An application is first developed with the relational model and then with the object-relational model.

This appendix contains the following sections:

- Introduction to the Sample Application

- Implementing the Schema on the Relational Model

- Implementing the Schema on the Object-Relational Model

- Evolving Object Types

## Introduction to the Sample Application

User-defined types are schema objects in which users formalize the data structures and operations that appear in their applications.

The examples in this appendix illustrate the most important aspects of defining, using, and evolving object types. One important aspect of working with object types is creating methods that perform operations on objects. In the example, definitions of object type methods use the PL/SQL language. Other aspects of using object types, such as defining a type, use SQL.

The examples develop different versions of a database schema for an application that manages customer purchase orders. First a purely relational version is shown, and then an equivalent, object-relational version. Both versions provide for the same basic kinds of entities—customers, purchase orders, line items, and so on. But the object-relational version creates object types for these entities and manages data for particular customers and purchase orders by instantiating instances of the respective object types.

PL/SQL and Java provide additional capabilities beyond those illustrated in this appendix, especially in the area of accessing and manipulating the elements of collections.

Client applications that use the Oracle Call Interface (OCI), Pro*C/C++, or Oracle Objects for OLE (OO4O) can take advantage of its extensive facilities for accessing objects and collections, and manipulating them on clients.

See Also: Oracle Database SQL Reference for a complete description of SQL syntax and usage for user-defined types Oracle Database PL/SQL User's Guide and Reference for a complete discussion of PL/SQL capabilities Oracle Database Java Developer's Guide for a complete discussion of Java Oracle Call Interface Programmer's Guide Pro*C/C++ Programmer's Guide

## Implementing the Schema on the Relational Model

This section implements the relational version of the purchase order schema depicted in Figure A-1.

### Entities and Relationships

The basic entities in this example are:

- Customers

- The stock of products for sale

- Purchase orders

As shown in Figure A-1, a customer has contact information, so that the address and set of telephone numbers is exclusive to that customer. The application does not allow different customers to be associated with the same address or telephone numbers. If a customer changes his address, the previous address ceases to exist. If someone ceases to be a customer, the associated address disappears.

A customer has a one-to-many relationship with a purchase order. A customer can place many orders, but a given purchase order is placed by one customer. Because a customer can be defined before he places an order, the relationship is optional rather than mandatory.

Similarly, a purchase order has a many-to-many relationship with a stock item. Because this relationship does not show which stock items appear on which purchase orders, the entity-relationship has the notion of a line item. A purchase order must contain one or more line items. Each line item is associated only with one purchase order. The relationship between line item and stock item is that a stock item can appear on zero, one, or many line items, but each line item refers to exactly one stock item.

Figure A-1 Entity-Relationship Diagram for Purchase Order Application

Description of the illustration adobj001.gif

### Creating Tables Under the Relational Model

The relational approach normalizes everything into tables. The table names are `Customer_reltab`, `PurchaseOrder_reltab`, and `Stock_reltab`.

Each part of an address becomes a column in the `Customer_reltab` table. Structuring telephone numbers as columns sets an arbitrary limit on the number of telephone numbers a customer can have.

The relational approach separates line items from their purchase orders and puts each into its own table, named `PurchaseOrder_reltab` and `LineItems_reltab`.

As depicted in Figure A-1, a line item has a relationship to both a purchase order and a stock item. These are implemented as columns in `LineItems_reltab` table with foreign keys to `PurchaseOrder_reltab` and `Stock_reltab`.

Note: We have adopted a convention in this section of adding the suffix `_reltab` to the names of relational tables. Such a self-describing notation can make your code easier to maintain. You may find it useful to make distinctions between tables (`_tab`) and types (`_typ`). But you can choose any names you want; one of the advantages of object-relational constructs is that you can give them names that closely model the corresponding real-world objects.

The relational approach results in the tables describe in the following sections.

#### Customer_reltab

The `Customer_reltab` table has the following definition:

Example A-1 Creating the Customer_reltab Table

    CREATE TABLE Customer_reltab (
      CustNo                NUMBER NOT NULL,
      CustName              VARCHAR2(200) NOT NULL,
      Street                VARCHAR2(200) NOT NULL,
      City                  VARCHAR2(200) NOT NULL,
      State                 CHAR(2) NOT NULL,
      Zip                   VARCHAR2(20) NOT NULL,
      Phone1                VARCHAR2(20),
      Phone2                VARCHAR2(20),
      Phone3                VARCHAR2(20),
      PRIMARY KEY (CustNo));

This table, `Customer_reltab`, stores all the information about customers, which means that it fully contains information that is intrinsic to the customer (defined with the `NOT` `NULL` constraint) and information that is not as essential. According to this definition of the table, the application requires that every customer have a shipping address.

Our Entity-Relationship (E-R) diagram showed a customer placing an order, but the table does not make allowance for any relationship between the customer and the purchase order. This relationship must be managed by the purchase order.

#### PurchaseOrder_reltab

The `PurchaseOrder_reltab` table has the following definition:

Example A-2 Creating the PurchaseOrder_reltab Table

    CREATE TABLE PurchaseOrder_reltab (
       PONo        NUMBER, /* purchase order no */
       Custno      NUMBER references Customer_reltab, /*  Foreign KEY referencing
                                                          customer */
       OrderDate   DATE, /*  date of order */
       ShipDate    DATE, /* date to be shipped */
       ToStreet    VARCHAR2(200), /* shipto address */
       ToCity      VARCHAR2(200),
       ToState     CHAR(2),
       ToZip       VARCHAR2(20),
       PRIMARY KEY(PONo));

`PurchaseOrder_reltab` manages the relationship between the customer and the purchase order by means of the foreign key (FK) column `CustNo`, which references the `CustNo` key of the `Customer_reltab`. The `PurchaseOrder_reltab` table contains no information about related line items. The line items table (next section) uses the purchase order number to relate a line item to its parent purchase order.

#### Stock_reltab

The `Stock_reltab` table has the following definition:

Example A-3 Creating the Stock_reltab Table

    CREATE TABLE Stock_reltab (
      StockNo      NUMBER PRIMARY KEY,
      Price        NUMBER,
      TaxRate      NUMBER);

#### LineItems_reltab

The `LineItems_reltab` table has the following definition:

Example A-4 Creating the LineItems_reltab Table

    CREATE TABLE LineItems_reltab (
      LineItemNo           NUMBER,
      PONo                 NUMBER REFERENCES PurchaseOrder_reltab,
      StockNo              NUMBER REFERENCES Stock_reltab,
      Quantity             NUMBER,
      Discount             NUMBER,
      PRIMARY KEY (PONo, LineItemNo));

Note: The `Stock_reltab` and `PurchaseOrder_reltab` tables must be created before the `LineItems_reltab` table.

The table name is in the plural form `LineItems_reltab` to emphasize to someone reading the code that the table holds a collection of line items.

As shown in the E-R diagram, the list of line items has relationships with both the purchase order and the stock item. These relationships are managed by `LineItems_reltab` by means of two foreign key columns:

- `PONo`, which references the `PONo` column in `PurchaseOrder_reltab`

- `StockNo`, which references the `StockNo` column in `Stock_reltab`

### Inserting Values Under the Relational Model

In our application, statements like these insert data into the tables:

Example A-5 Establish Inventory

    INSERT INTO Stock_reltab VALUES(1004, 6750.00, 2);
    INSERT INTO Stock_reltab VALUES(1011, 4500.23, 2);
    INSERT INTO Stock_reltab VALUES(1534, 2234.00, 2);
    INSERT INTO Stock_reltab VALUES(1535, 3456.23, 2);

Example A-6 Register Customers

    INSERT INTO Customer_reltab
      VALUES (1, 'Jean Nance', '2 Avocet Drive',
             'Redwood Shores', 'CA', '95054',
             '415-555-1212', NULL, NULL);

    INSERT INTO Customer_reltab
      VALUES (2, 'John Nike', '323 College Drive',
             'Edison', 'NJ', '08820',
             '609-555-1212', '201-555-1212', NULL);

Example A-7 Place Orders

    INSERT INTO PurchaseOrder_reltab
      VALUES (1001, 1, SYSDATE, '10-MAY-1997',
              NULL, NULL, NULL, NULL);

    INSERT INTO PurchaseOrder_reltab
      VALUES (2001, 2, SYSDATE, '20-MAY-1997',
             '55 Madison Ave', 'Madison', 'WI', '53715');

Example A-8 Detail Line Items

    INSERT INTO LineItems_reltab VALUES(01, 1001, 1534, 12,  0);
    INSERT INTO LineItems_reltab VALUES(02, 1001, 1535, 10, 10);
    INSERT INTO LineItems_reltab VALUES(01, 2001, 1004,  1,  0);
    INSERT INTO LineItems_reltab VALUES(02, 2001, 1011,  2,  1);

### Querying Data Under the Relational Model

The application can execute queries like these:

Example A-9 Get Customer and Line Item Data for a Specific Purchase Order

    SELECT   C.CustNo, C.CustName, C.Street, C.City, C.State,
             C.Zip, C.phone1, C.phone2, C.phone3,
             P.PONo, P.OrderDate,
             L.StockNo, L.LineItemNo, L.Quantity, L.Discount
     FROM    Customer_reltab C,
             PurchaseOrder_reltab P,
             LineItems_reltab L
     WHERE   C.CustNo = P.CustNo
      AND    P.PONo = L.PONo
      AND    P.PONo = 1001;

Example A-10 Get the Total Value of Purchase Orders

    SELECT     P.PONo, SUM(S.Price * L.Quantity)
     FROM      PurchaseOrder_reltab P,
               LineItems_reltab L,
               Stock_reltab S
     WHERE     P.PONo = L.PONo
      AND      L.StockNo = S.StockNo
     GROUP BY P.PONo;

Example A-11 Get the Purchase Order and Line Item Data for Stock Item 1004

    SELECT    P.PONo, P.CustNo,
              L.StockNo, L.LineItemNo, L.Quantity, L.Discount
     FROM     PurchaseOrder_reltab P,
              LineItems_reltab     L
     WHERE    P.PONo = L.PONo
       AND    L.StockNo = 1004;

### Updating Data Under the Relational Model

The application can execute statements like these to update the data:

Example A-12 Update the Quantity for Purchase Order 1001 and Stock Item 1534

    UPDATE LineItems_reltab
       SET      Quantity = 20
       WHERE    PONo     = 1001
       AND      StockNo  = 1534;

### Deleting Data Under the Relational Model

The application can execute statements similar to Example A-13 to delete data.

Example A-13 Delete Purchase Order 1001 under the Relational Model

    DELETE
       FROM   LineItems_reltab
       WHERE  PONo = 1001;

    DELETE
       FROM   PurchaseOrder_reltab
       WHERE  PONo = 1001;

## Implementing the Schema on the Object-Relational Model

The object-relational approach begins with the same entity relationships as in "Entities and Relationships". Viewing these from the object-oriented perspective, as in the following class diagram, allows us to translate more of the real-world structure into the database schema.

Figure A-2 Class Diagram for Purchase Order Application

Description of the illustration adobj002.gif

Instead of breaking up addresses or multiple phone numbers into unrelated columns in relational tables, the object-relational approach defines types to represent an entire address and an entire list of phone numbers. Similarly, the object-relational approach uses nested tables to keep line items with their purchase orders instead of storing them separately.

The main entities—customers, stock, and purchase orders—become object types. Object references are used to express some of the relationships among them. Collection types—varrays and nested tables—are used to model multi-valued attributes.

Note: This appendix implements an object-relational interface by building an object-relational schema from scratch. On this approach, we create object tables for data storage. Alternatively, instead of object tables, you can use object views to implement an object-relational interface to existing data stored in relational tables. Chapter 5 discusses object views.

### Defining Types

You create an object type with a `CREATE` `TYPE` statement. For example, the following statement creates the type `StockItem_objtyp`:

Example A-14 Creating the StockItem_objtyp Object

    CREATE TYPE StockItem_objtyp AS OBJECT (
      StockNo    NUMBER,
      Price      NUMBER,
      TaxRate    NUMBER
      );
    /

Instances of type `StockItem_objtyp` are objects representing the stock items that customers order. They have three numeric attributes. `StockNo` is the primary key.

The order in which you define types can make a difference. Ideally, you want to wait to define types that refer to other types until you have defined the other types they refer to.

For example, the type `LineItem_objtyp` refers to, and thus presupposes, `StockItem_objtyp` by containing an attribute that is a `REF` to objects of `StockItem_objtyp`. You can see this in the statement that creates the type `LineItem_objtyp`:

Example A-15 Creating the LineItem_objtyp Object

    CREATE TYPE LineItem_objtyp AS OBJECT (
      LineItemNo   NUMBER,
      Stock_ref    REF StockItem_objtyp,
      Quantity     NUMBER,
      Discount     NUMBER
      );
    /

Instances of type `LineItem_objtyp` are objects that represent line items. They have three numeric attributes and one `REF` attribute. The `LineItem_objtyp` models the line item entity and includes an object reference to the corresponding stock object.

Sometimes the web of references among types makes it difficult or impossible to avoid creating a type before all the types that it presupposes are created. To deal with this sort of situation, you can create what is called an incomplete type to use as a placeholder for other types that you want to create to refer to. Then, when you have created the other types, you can come back and replace the incomplete type with a complete one.

For example, if we had needed to create `LineItem_objtyp` before we created `StockItem_objtyp`, we could have used a statement like the following to create `LineItem_objtyp` as an incomplete type:

`CREATE` `TYPE` `LineItem_objtyp;`

The form of the `CREATE` `TYPE` statement used to create an incomplete type lacks that phrase `AS` `OBJECT` and also lacks the specification of attributes.

To replace an incomplete type with a complete definition, include the phrase `OR` `REPLACE` as shown in the following example:

Example A-16 Replacing the LineItem_objtyp Object

    CREATE OR REPLACE TYPE LineItem_objtyp AS OBJECT (
      LineItemNo   NUMBER,
      Stock_ref    REF StockItem_objtyp,
      Quantity     NUMBER,
      Discount     NUMBER
      );
    /

It is never wrong to include the words `OR` `REPLACE`, even if you have no incomplete type to replace.

Now create the remaining types we need for the schema. The following statement defines an array type for the list of phone numbers:

Example A-17 Creating the PhoneList_vartyp Type

    CREATE TYPE PhoneList_vartyp AS VARRAY(10) OF VARCHAR2(20);
    /

Any data unit, or instance, of type `PhoneList_vartyp` is a varray of up to 10 telephone numbers, each represented by a data item of type `VARCHAR2`.

Either a varray or a nested table could be used to contain a list of phone numbers. In this case, the list is the set of contact phone numbers for a single customer. A varray is a better choice than a nested table for the following reasons:

- The order of the numbers might be important: varrays are ordered while nested tables are unordered.

- The number of phone numbers for a specific customer is small. Varrays force you to specify a maximum number of elements (10 in this case) in advance. They use storage more efficiently than nested tables, which have no special size limitations.

- You can query a nested table but not a varray. But there is no reason to query the phone number list, so using a nested table offers no benefit.

In general, if ordering and bounds are not important design considerations, then designers can use the following rule of thumb for deciding between varrays and nested tables: If you need to query the collection, then use nested tables; if you intend to retrieve the collection as a whole, then use varrays.

See Also: Chapter 8, "Design Considerations for Oracle Objects" for more information about the design considerations for varrays and nested tables

The following statement defines the object type `Address_objtyp` to represent addresses:

Example A-18 Creating the Address_objtyp Object

    CREATE TYPE Address_objtyp AS OBJECT (
      Street         VARCHAR2(200),
      City           VARCHAR2(200),
      State          CHAR(2),
      Zip            VARCHAR2(20)
      )
    /

All of the attributes of an address are character strings, representing the usual parts of a simplified mailing address.

The following statement defines the object type `Customer_objtyp`, which uses other object types as building blocks.

Example A-19 Creating the Customer_objtyp Object

    CREATE TYPE Customer_objtyp AS OBJECT (
      CustNo           NUMBER,
      CustName         VARCHAR2(200),
      Address_obj      Address_objtyp,
      PhoneList_var    PhoneList_vartyp,

      ORDER MEMBER FUNCTION
        compareCustOrders(x IN Customer_objtyp) RETURN INTEGER
    ) NOT FINAL;
    /

Instances of the type `Customer_objtyp` are objects that represent blocks of information about specific customers. The attributes of a `Customer_objtyp` object are a number, a character string, an `Address_objtyp` object, and a varray of type `PhoneList_vartyp`.

The clause `NOT` `FINAL` enables us to create subtypes of the customer type later if we wish. By default, types are created as `FINAL`, which means that the type cannot be further specialized by deriving subtypes from it. We define a subtype of `Customer_objtyp` for a more specialized kind of customer later in this appendix.

Every `Customer_objtyp` object also has an associated order method, one of the two types of comparison methods. Whenever Oracle needs to compare two `Customer_objtyp` objects, it implicitly invokes the `compareCustOrders` method to do so.

Note: The PL/SQL to implement the comparison method appears in "The compareCustOrders Method".

The two types of comparison methods are map methods and order methods. This application uses one of each for purposes of illustration.

An `ORDER` method must be called for every two objects being compared, whereas a map method is called once for each object. In general, when sorting a set of objects, the number of times an `ORDER` method is called is more than the number of times a map method would be called.

See Also: Chapter 2, "Basic Components of Oracle Objects" for more information about map and order methods Oracle Database PL/SQL User's Guide and Reference for details about how to use pragma declarations

The following statement defines a type for a nested table of line items. Each purchase order will use an instance of this nested table type to contain the line items for that purchase order:

Example A-20 Creating the LineItemList_ntabtyp Type

    CREATE TYPE LineItemList_ntabtyp AS TABLE OF LineItem_objtyp;
    /

An instance of this type is a nested table object (in other words, a nested table), each row of which contains an object of type `LineItem_objtyp`. A nested table of line items is a better choice to represent the multivalued line item list than a varray of `LineItem_objtyp` objects, because:

- Most applications will need to query the contents of line items. This can be done using SQL if the line items are stored in a nested table but not if they are stored in a varray.

- If an application needs to index on line item data, this can be done with nested tables but not with varrays.

- The order in which line items are stored is probably not important, and a query can order them by line item number when necessary.

- There is no practical upper bound on the number of line items on a purchase order. Using a varray requires specifying an arbitrary upper bound on the number of elements.

The following statement defines the object type `PurchaseOrder_objtyp`:

Example A-21 Creating the PurchaseOrder_objtyp Object

    CREATE TYPE PurchaseOrder_objtyp AUTHID CURRENT_USER AS OBJECT (
      PONo                 NUMBER,
      Cust_ref             REF Customer_objtyp,
      OrderDate            DATE,
      ShipDate             DATE,
      LineItemList_ntab    LineItemList_ntabtyp,
      ShipToAddr_obj       Address_objtyp,

      MAP MEMBER FUNCTION
        getPONo RETURN NUMBER,

      MEMBER FUNCTION
        sumLineItems RETURN NUMBER
      );
    /

Instances of type `PurchaseOrder_objtyp` are objects representing purchase orders. They have six attributes, including a `REF` to `Customer_objtyp`, an `Address_objtyp` object, and a nested table of type `LineItemList_ntabtyp`, which is based on type `LineItem_objtyp`. `PONo` is the primary key and `Cust_ref` is a foreign key.

Objects of type `PurchaseOrder_objtyp` have two methods: `getPONo` and `sumLineItems`. One, `getPONo`, is a map method, one of the two kinds of comparison methods. A map method returns the relative position of a given record within the order of records within the object. So, whenever Oracle needs to compare two `PurchaseOrder_objtyp` objects, it implicitly calls the `getPONo` method to do so.

The two pragma declarations provide information to PL/SQL about what sort of access the two methods need to the database.

The statement does not include the actual PL/SQL programs implementing the methods `getPONo` and `sumLineItems`. Those appear in "Method Definitions".

### Method Definitions

If a type has no methods, its definition consists just of a `CREATE` `TYPE` statement. However, for a type that has methods, you must also define a type body to complete the definition of the type. You do this with a `CREATE` `TYPE` `BODY` statement. As with `CREATE` `TYPE`, you can include the words `OR` `REPLACE`. You must include this phrase if you are replacing an existing type body with a new one, to change the methods.

The following statement defines the body of the type `PurchaseOrder_objtyp`. The statement supplies the PL/SQL programs that implement the type's methods:

Example A-22 Creating the PurchaseOrder_objtyp Type Body

    CREATE OR REPLACE TYPE BODY PurchaseOrder_objtyp AS

    MAP MEMBER FUNCTION getPONo RETURN NUMBER is
       BEGIN
          RETURN PONo;
       END;

    MEMBER FUNCTION sumLineItems RETURN NUMBER is
          i             INTEGER;
          StockVal      StockItem_objtyp;
          Total         NUMBER := 0;

       BEGIN
          FOR i in 1..SELF.LineItemList_ntab.COUNT LOOP
             UTL_REF.SELECT_OBJECT(LineItemList_ntab(i).Stock_ref,StockVal);
             Total := Total + SELF.LineItemList_ntab(i).Quantity * StockVal.Price;
          END LOOP;
          RETURN Total;
       END;
    END;
    /

#### The getPONo Method

The `getPONo` method simply returns the value of the `PONo` attribute—namely, the purchase order number—of whatever instance of the type `PurchaseOrder_objtyp` that calls the method. Such `get` methods allow you to avoid reworking code that uses the object if its internal representation changes.

#### The sumLineItems Method

The `sumLineItems` method uses a number of object-relational features:

- As already noted, the basic function of the `sumLineItems` method is to return the sum of the values of the line items of its associated `PurchaseOrder_objtyp` object. The keyword `SELF`, which is implicitly created as a parameter to every function, lets you refer to that object.

- The keyword `COUNT` gives the count of the number of elements in a PL/SQL table or array. Here, in combination with `LOOP`, the application iterates through all the elements in the collection — in this case, the items of the purchase order. In this way `SELF`.`LineItemList_ntab`.`COUNT` counts the number of elements in the nested table that match the `LineItemList_ntab` attribute of the `PurchaseOrder_objtyp` object, here represented by `SELF`.

- A method from package `UTL_REF` is used in the implementation. The `UTL_REF` methods are necessary because Oracle does not support implicit dereferencing of `REF`s within PL/SQL programs. The `UTL_REF` package provides methods that operate on object references. Here, the `SELECT_OBJECT` method is called to obtain the `StockItem_objtyp` object corresponding to the `Stock_ref`.

The `AUTHID` `CURRENT_USER` syntax specifies that the `PurchaseOrder_objtyp` is defined using invoker rights: the methods are executed under the rights of the current user, not under the rights of the user who defined the type.

- The PL/SQL variable `StockVal` is of type `StockItem_objtyp`. The `UTL_REF`.`SELECT_OBJECT` sets it to the object whose reference is the following:

  `(LineItemList_ntab(i).Stock_ref)`

  This object is the actual stock item referred to in the currently selected line item.

- Having retrieved the stock item in question, the next step is to compute its cost. The program refers to the stock item's cost as `StockVal`.`Price`, the `Price` attribute of the `StockItem_objtyp` object. But to compute the cost of the item, you also need to know the quantity of items ordered. In the application, the term `LineItemList_ntab(i)`.`Quantity` represents the `Quantity` attribute of the currently selected `LineItem_objtyp` object.

The remainder of the method program is a loop that sums the values of the line items. The method returns the total.

#### The compareCustOrders Method

The following statement defines the `compareCustOrders` method in the type body of the `Customer_objtyp` object type:

Example A-23 Creating the Customer_objtyp Type Body

    CREATE OR REPLACE TYPE BODY Customer_objtyp AS
      ORDER MEMBER FUNCTION
      compareCustOrders (x IN Customer_objtyp) RETURN INTEGER IS
      BEGIN
        RETURN CustNo - x.CustNo;
      END;
    END;
    /

As mentioned earlier, the order method `compareCustOrders` operation compares information about two customer orders. It takes another `Customer_objtyp` object as an input argument and returns the difference of the two `CustNo` numbers. The return value is:

- a negative number if its own object has a smaller value of `CustNo`

- a positive number if its own object has a larger value of `CustNo`

- zero if the two objects have the same value of `CustNo`—in which case both orders are associated with the same customer.

Whether the return value is positive, negative, or zero signifies the relative order of the customer numbers. For example, perhaps lower numbers are created earlier in time than higher numbers. If either of the input arguments (`SELF` and the explicit argument) to an `ORDER` method is `NULL`, Oracle does not call the `ORDER` method and simply treats the result as `NULL`.

We have now defined all of the object types for the object-relational version of the purchase order schema. We have not yet created any instances of these types to contain actual purchase order data, nor have we created any tables in which to store such data. We show how to do this in the next section.

### Creating Object Tables

Creating an object type is not the same as creating a table. Creating a type merely defines a logical structure; it does not create storage. To use an object-relational interface to your data, you must create object types whether you intend to store your data in object tables or leave it in relational tables and access it through object views. Object views and object tables alike presuppose object types: an object table or object view is always a table or view of a certain object type. In this respect it is like a relational column, which always has a specified data type.

See Also: Chapter 5, "Applying an Object Model to Relational Data" for a discussion of object views

Like a relational column, an object table can contain rows of just one kind of thing, namely, object instances of the same declared type as the table. (And, if the table is substitutable, it can contain instances of subtypes of its declared type as well.)

Each row in an object table is a single object instance. So, in one sense, an object table has, or consists of, only a single column of the declared object type. But this is not as different as it may seem from the case with relational tables. Each row in a relational table theoretically represents a single entity as well—for example, a customer, in a relational `Customers` table. The columns of a relational table store data for attributes of this entity.

Similarly, in an object table, attributes of the object type map to columns that can be inserted into and selected from. The major difference is that, in an object table, data is stored—and can be retrieved—in the structure defined by the table's type, making it possible for you to retrieve an entire, multilevel structure of data with a very simple query.

#### The Object Table Customer_objtab

The following statement defines an object table `Customer_objtab` to hold objects of type `Customer_objtyp`:

Example A-24 Creating the Customer_objtab Table

    CREATE TABLE Customer_objtab OF Customer_objtyp (CustNo PRIMARY KEY)
       OBJECT IDENTIFIER IS PRIMARY KEY;

Unlike with relational tables, when you create an object table, you specify a data type for it, namely, the type of objects it will contain.

The table has a column for each attribute of `Customer_objtyp`, namely:

`CustNo NUMBER /* Primary key */`
`CustName VARCHAR2(200)`
`Address_obj Address_objtyp`
`PhoneList_var PhoneList_vartyp`

See Example A-18, "Creating the Address_objtyp Object" and Example A-17, "Creating the PhoneList_vartyp Type" for the definitions of those types.

Figure A-3 Object Relational Representation of Table Customer_objtab

Description of the illustration adobj008.gif

### Object Datatypes as a Template for Object Tables

Because there is a type `Customer_objtyp`, you could create numerous object tables of the same type. For example, you could create an object table `Customer_objtab2` also of type `Customer_objtyp`.

You can introduce variations when creating multiple tables. The statement that created `Customer_objtab` defined a primary key constraint on the `CustNo` column. This constraint applies only to this object table. Another object table of the same type might not have this constraint.

### Object Identifiers and References

`Customer_objtab` contains customer objects, represented as row objects. Oracle allows row objects to be referenceable, meaning that other row objects or relational rows may reference a row object using its object identifier (OID). For example, a purchase order row object may reference a customer row object using its object reference. The object reference is a system-generated value represented by the type `REF` and is based on the row object's unique OID.

Oracle requires every row object to have a unique OID. You may specify the unique OID value to be system-generated or specify the row object's primary key to serve as its unique OID. You indicate this when you execute the `CREATE` `TABLE` statement by specifying `OBJECT` `IDENTIFIER` `IS` `PRIMARY` `KEY` or `OBJECT` `IDENTIFIER` `IS` `SYSTEM` `GENERATED`. The latter is the default. Using the primary key as the object identifier can be more efficient in cases where the primary key value is smaller than the default 16 byte system-generated identifier. For our example, the primary key is used as the row object identifier.

### Object Tables with Embedded Objects

Note that the `Address_obj` column of `Customer_objtab` contains `Address_objtyp` objects. As this shows, an object type may have attributes that are themselves object types. Object instances of the declared type of an object table are called row objects because one object instance occupies an entire row of the table. But embedded objects such as those in the `Address_obj` column are referred to as column objects. These differ from row objects in that they do not take up an entire row. Consequently, they are not referenceable—they cannot be the target of a `REF`. Also, they can be `NULL`.

The attributes of `Address_objtyp` objects are of built-in types. They are scalar rather than complex (that is, they are not object types with attributes of their own), and so are called leaf-level attributes to reflect that they represent an end to branching. Columns for `Address_objtyp` objects and their attributes are created in the object table `Customer_objtab`. You can refer or navigate to these columns using the dot notation. For example, if you want to build an index on the `Zip` column, you can refer to it as `Address`.`Zip`.

The `PhoneList_var` column contains varrays of type `PhoneList_vartyp`. We defined each object of type `PhoneList_vartyp` as a varray of up to 10 telephone numbers, each represented by a data item of type `VARCHAR2`. See Example A-17.

Because each varray of type `PhoneList_vartyp` can contain no more than 200 characters (10 x 20), plus a small amount of overhead, Oracle stores the varray as a single data unit in the `PhoneList_var` column. Oracle stores varrays that do not exceed 4000 bytes in inline `BLOB`s, which means that a portion of the varray value could potentially be stored outside the table.

#### The Object Table Stock_objtab

The next statement creates an object table for `StockItem_objtyp` objects:

Example A-25 Creating the Stock_objtab Table

    CREATE TABLE Stock_objtab OF StockItem_objtyp (StockNo PRIMARY KEY)
       OBJECT IDENTIFIER IS PRIMARY KEY;

Each row of the table is a `StockItem_objtyp` object having three numeric attributes:

`StockNo NUMBER`
`Price NUMBER`
`TaxRate NUMBER`

Oracle creates a column for each attribute. The `CREATE` `TABLE` statement places a primary key constraint on the `StockNo` column and specifies that the primary key be used as the row object's identifier.

#### The Object Table PurchaseOrder_objtab

The next statement defines an object table for `PurchaseOrder_objtyp` objects:

Example A-26 Creating the PurchaseOrder_objtab Table

    CREATE TABLE PurchaseOrder_objtab OF PurchaseOrder_objtyp (  /* Line 1 */
       PRIMARY KEY (PONo),                                       /* Line 2 */
       FOREIGN KEY (Cust_ref) REFERENCES Customer_objtab)        /* Line 3 */
       OBJECT IDENTIFIER IS PRIMARY KEY                          /* Line 4 */
       NESTED TABLE LineItemList_ntab STORE AS PoLine_ntab (     /* Line 5 */
         (PRIMARY KEY(NESTED_TABLE_ID, LineItemNo))              /* Line 6 */
         ORGANIZATION INDEX COMPRESS)                            /* Line 7 */
       RETURN AS LOCATOR                                         /* Line 8 */
    /

The preceding `CREATE` `TABLE` statement creates the `PurchaseOrder_objtab` object table. The significance of each line is as follows:

Line 1:

`CREATE` `TABLE` `PurchaseOrder_objtab` `OF` `PurchaseOrder_objtyp` `(`

This line indicates that each row of the table is a `PurchaseOrder_objtyp` object. Attributes of `PurchaseOrder_objtyp` objects are:

`PONo NUMBER`
`Cust_ref REF Customer_objtyp`
`OrderDate DATE`
`ShipDate DATE`
`LineItemList_ntab LineItemList_ntabtyp`
`ShipToAddr_obj Address_objtyp`

See Example A-19, "Creating the Customer_objtyp Object" and Example A-20, "Creating the LineItemList_ntabtyp Type" for the definitions of those types.

Figure A-4 Object Relational Representation of Table PurchaseOrder_objtab

Description of the illustration adobj009.gif

Line 2:

`PRIMARY` `KEY` `(PONo),`

This line specifies that the `PONo` attribute is the primary key for the table.

Line 3:

`FOREIGN` `KEY` `(Cust_ref)` `REFERENCES` `Customer_objtab)`

This line specifies a referential constraint on the `Cust_ref` column. This referential constraint is similar to those specified for relational tables. When there is no constraint, the `REF` column permits you to reference any row object. However, in this case, the `Cust_ref` `REF`s can refer only to row objects in the `Customer_objtab` object table.

Line 4:

`OBJECT` `IDENTIFIER` `IS` `PRIMARY` `KEY`

This line indicates that the primary key of the `PurchaseOrder_objtab` object table be used as the row's OID.

Line 5 - 8:

`NESTED` `TABLE` `LineItemList_ntab` `STORE` `AS` `PoLine_ntab` `(`
`(PRIMARY KEY(NESTED_TABLE_ID, LineItemNo))`
`ORGANIZATION INDEX COMPRESS)`
`RETURN AS LOCATOR`

These lines pertain to the storage specification and properties of the nested table column, `LineItemList_ntab`. The rows of a nested table are stored in a separate storage table. This storage table cannot be directly queried by the user but can be referenced in DDL statements for maintenance purposes. A hidden column in the storage table, called the `NESTED_TABLE_ID,` matches the rows with their corresponding parent row. All the elements in the nested table belonging to a particular parent have the same `NESTED_TABLE_ID` value. For example, all the elements of the nested table of a given row of `PurchaseOrder_objtab` have the same value of `NESTED_TABLE_ID`. The nested table elements that belong to a different row of `PurchaseOrder_objtab` have a different value of `NESTED_TABLE_ID`.

In the preceding `CREATE` `TABLE` example, Line 5 indicates that the rows of `LineItemList_ntab` nested table are to be stored in a separate table (referred to as the storage table) named `PoLine_ntab`. The `STORE` `AS` clause also permits you to specify the constraint and storage specification for the storage table. In this example, Line 7 indicates that the storage table is an index-organized table (`IOT`). In general, storing nested table rows in an IOT is beneficial because it provides clustering of rows belonging to the same parent. The specification of `COMPRESS` on the `IOT` saves storage space because, if you do not specify `COMPRESS`, the `NESTED_TABLE_ID` part of the `IOT`'s key is repeated for every row of a parent row object. If, however, you specify `COMPRESS`, the `NESTED_TABLE_ID` is stored only once for each parent row object.

See Also: "Nested Table Storage" for information about the benefits of organizing a nested table as an IOT, specifying nested table compression, and for more information about nested table storage in general

In Line 6, the specification of `NESTED_TABLE_ID` and `LineItemNo` attribute as the primary key for the storage table serves two purposes: first, it specifies the key for the `IOT`; second, it enforces uniqueness of the column `LineItemNo` of the nested table within each row of the parent table. By including the `LineItemNo` column in the key, the statement ensures that the `LineItemNo` column contains distinct values within each purchase order.

Line 8 indicates that the nested table, `LineItemList_ntab`, is returned in the locator form when retrieved. If you do not specify `LOCATOR`, the default is `VALUE`, which causes the entire nested table to be returned instead of just a locator to it. If a nested table collection contains many elements, it is inefficient to return the entire nested table whenever the containing row object or the column is selected.

Specifying that the nested table's locator is returned enables Oracle to send the client only a locator to the actual collection value. An application can find whether a fetched nested table is in the locator or value form by calling the `OCICollIsLocator` or `UTL_COLL`.`IS_LOCATOR` interfaces. Once you know that the locator has been returned, the application can query using the locator to fetch only the desired subset of row elements in the nested table. This locator-based retrieval of the nested table rows is based on the original statement's snapshot, to preserve the value or copy semantics of the nested table. That is, when the locator is used to fetch a subset of row elements in the nested table, the nested table snapshot reflects the nested table when the locator was first retrieved.

Recall the implementation of the `sumLineItems` method of `PurchaseOrder_objtyp` in "Method Definitions". That implementation assumed that the `LineItemList_ntab` nested table would be returned as a `VALUE`. In order to handle large nested tables more efficiently, and to take advantage of the fact that the nested table in the `PurchaseOrder_objtab` is returned as a locator, the `sumLineItems` method must be rewritten as follows:

Example A-27 Replacing the PurchaseOrder_objtyp Type Body

    CREATE OR REPLACE TYPE BODY PurchaseOrder_objtyp AS

       MAP MEMBER FUNCTION getPONo RETURN NUMBER is
          BEGIN
             RETURN PONo;
          END;

       MEMBER FUNCTION sumLineItems RETURN NUMBER IS
          i          INTEGER;
          StockVal   StockItem_objtyp;
          Total      NUMBER := 0;

       BEGIN
          IF (UTL_COLL.IS_LOCATOR(LineItemList_ntab)) -- check for locator
             THEN
                SELECT SUM(L.Quantity * L.Stock_ref.Price) INTO Total
                FROM   TABLE(CAST(LineItemList_ntab AS LineItemList_ntabtyp)) L;
          ELSE
             FOR i in 1..SELF.LineItemList_ntab.COUNT LOOP
                UTL_REF.SELECT_OBJECT(LineItemList_ntab(i).Stock_ref,StockVal);
                Total := Total + SELF.LineItemList_ntab(i).Quantity *
                                                                StockVal.Price;
             END LOOP;
          END IF;
       RETURN Total;
       END;
    END;
    /

The rewritten `sumLineItems` method checks whether the nested table attribute, `LineItemList_ntab`, is returned as a locator using the `UTL_COLL`.`IS_LOCATOR` function. If the condition evaluates to `TRUE`, the nested table locator is queried using the `TABLE` expression.

Note: The `CAST` expression is currently required in such `TABLE` expressions to tell the SQL compilation engine the actual type of the collection attribute (or parameter or variable) so that it can compile the query.

The querying of the nested table locator results in more efficient processing of the large line item list of a purchase order. The previous code that iterates over the `LineItemList_ntab` is kept to deal with the case where the nested table is returned as a `VALUE`.

After the table is created, the `ALTER` `TABLE` statement is issued to add the `SCOPE` `FOR` constraint on a `REF`. The `SCOPE` `FOR` constraint on a `REF` is not allowed in a `CREATE` `TABLE` statement. To specify that `Stock_ref` can reference only the object table `Stock_objtab`, issue the following `ALTER` `TABLE` statement on the `PoLine_ntab` storage table:

Example A-28 Adding the SCOPE FOR Constraint

    ALTER TABLE PoLine_ntab
       ADD (SCOPE FOR (Stock_ref) IS stock_objtab) ;

This statement specifies that the `Stock_ref` column of the nested table is scoped to `Stock_objtab`. This indicates that the values stored in this column must be references to row objects in `Stock_objtab`. The `SCOPE` constraint is different from the referential constraint in that the `SCOPE` constraint has no dependency on the referenced object. For example, any referenced row object in `Stock_objtab` may be deleted, even if it is referenced in the `Stock_ref` column of the nested table. Such a deletion renders the corresponding reference in the nested table a `DANGLING REF`.

Figure A-5 Object Relational Representation of Nested Table LineItemList_ntab

Description of the illustration adobj010.gif

Oracle does not support a referential constraint specification for storage tables. In this situation, specifying the `SCOPE` clause for a `REF` column is useful. In general, specifying scope or referential constraints for `REF` columns has several benefits:

- It saves storage space because it allows Oracle to store just the row object's unique identifier as the `REF` value in the column.

- It enables an index to be created on the storage table's `REF` column.

- It allows Oracle to rewrite queries containing dereferences of these `REF`s as joins involving the referenced table.

At this point, all of the tables for the purchase order application are in place. The next section shows how to operate on these tables.

Figure A-6 Object Relational Representation of Table PurchaseOrder_objtab

Description of the illustration adobj011.gif

#### Inserting Values

Here is how to insert the same data into the object tables that we inserted earlier into relational tables. Notice how some of the values incorporate calls to the constructors for object types, to create instances of the types.

Example A-29 Inserting Values in Stock_objtab

    INSERT INTO Stock_objtab VALUES(1004, 6750.00, 2) ;
    INSERT INTO Stock_objtab VALUES(1011, 4500.23, 2) ;
    INSERT INTO Stock_objtab VALUES(1534, 2234.00, 2) ;
    INSERT INTO Stock_objtab VALUES(1535, 3456.23, 2) ;

Example A-30 Inserting Values in Customer_objtab

    INSERT INTO Customer_objtab
      VALUES (
        1, 'Jean Nance',
        Address_objtyp('2 Avocet Drive', 'Redwood Shores', 'CA', '95054'),
        PhoneList_vartyp('415-555-1212')
        ) ;

    INSERT INTO Customer_objtab
      VALUES (
        2, 'John Nike',
        Address_objtyp('323 College Drive', 'Edison', 'NJ', '08820'),
        PhoneList_vartyp('609-555-1212','201-555-1212')
        ) ;

Example A-31 Inserting Values in PurchaseOrder_objtab

    INSERT INTO PurchaseOrder_objtab
      SELECT  1001, REF(C),
              SYSDATE, '10-MAY-1999',
              LineItemList_ntabtyp(),
              NULL
       FROM   Customer_objtab C
       WHERE  C.CustNo = 1 ;

The preceding statement constructs a `PurchaseOrder_objtyp` object with the following attributes:

`PONo 1001`
`Cust_ref REF to customer number 1`
`OrderDate SYSDATE`
`ShipDate 10-MAY-1999`
`LineItemList_ntab an empty LineItem_ntabtyp`
`ShipToAddr_obj NULL`

The statement uses a query to construct a `REF` to the row object in the `Customer_objtab` object table that has a `CustNo` value of `1`.

The following statement uses a `TABLE` expression to identify the nested table as the target for the insertion, namely the nested table in the `LineItemList_ntab` column of the row object in the `PurchaseOrder_objtab` table that has a `PONo` value of 1001.

Example A-32 Inserting Values in LineItemList_ntab

    INSERT INTO TABLE (
      SELECT  P.LineItemList_ntab
       FROM   PurchaseOrder_objtab P
       WHERE  P.PONo = 1001
      )
      SELECT  01, REF(S), 12, 0
       FROM   Stock_objtab S
       WHERE  S.StockNo = 1534 ;

The preceding statement inserts a line item into the nested table identified by the `TABLE` expression. The inserted line item contains a `REF` to the row object with a `StockNo` value of `1534` in the object table `Stock_objtab`.

The following statements follow the same pattern as the previous ones:

Example A-33 Inserting Values in PurchaseOrder_objtab and LineItemList_ntab

    INSERT INTO PurchaseOrder_objtab
      SELECT  2001, REF(C),
              SYSDATE, '20-MAY-1997',
              LineItemList_ntabtyp(),
              Address_objtyp('55 Madison Ave','Madison','WI','53715')
       FROM   Customer_objtab C
       WHERE  C.CustNo = 2 ;

    INSERT INTO TABLE (
      SELECT  P.LineItemList_ntab
       FROM   PurchaseOrder_objtab P
       WHERE  P.PONo = 1001
      )
      SELECT  02, REF(S), 10, 10
       FROM   Stock_objtab S
       WHERE  S.StockNo = 1535 ;

    INSERT INTO TABLE (
      SELECT  P.LineItemList_ntab
       FROM   PurchaseOrder_objtab P
       WHERE  P.PONo = 2001
      )
      SELECT  10, REF(S), 1, 0
       FROM   Stock_objtab S
       WHERE  S.StockNo = 1004 ;

    INSERT INTO TABLE (
      SELECT  P.LineItemList_ntab
       FROM   PurchaseOrder_objtab P
       WHERE  P.PONo = 2001
      )
      VALUES(11, (SELECT REF(S)
        FROM  Stock_objtab S
        WHERE S.StockNo = 1011), 2, 1) ;

#### Querying

The following query statement implicitly invokes a comparison method. It shows how Oracle orders objects of type `PurchaseOrder_objtyp` using that type's comparison method:

Example A-34 Query Purchase Orders

    SELECT  p.PONo
     FROM   PurchaseOrder_objtab p
     ORDER BY VALUE(p) ;

Oracle invokes the map method `getPONo` for each `PurchaseOrder_objtyp` object in the selection. Because that method returns the object's `PONo` attribute, the selection produces a list of purchase order numbers in ascending numerical order.

The following queries correspond to the queries executed under the relational model.

Example A-35 Query Customer and Line Item Data for Purchase Order 1001

    SELECT  DEREF(p.Cust_ref), p.ShipToAddr_obj, p.PONo,
            p.OrderDate, LineItemList_ntab
     FROM   PurchaseOrder_objtab p
     WHERE  p.PONo = 1001 ;

Example A-36 Query Total Value of Each Purchase Order

    SELECT   p.PONo, p.sumLineItems()
     FROM    PurchaseOrder_objtab p ;

Example A-37 Query Purchase Order and Line Item Data for Stock Item 1004

    SELECT   po.PONo, po.Cust_ref.CustNo,
             CURSOR (
               SELECT  *
                FROM   TABLE (po.LineItemList_ntab) L
                WHERE  L.Stock_ref.StockNo = 1004
               )
     FROM    PurchaseOrder_objtab po ;

The preceding query returns a nested cursor for the set of `LineItem_obj` objects selected from the nested table. The application can fetch from the nested cursor to get the individual `LineItem_obj` objects. The query can also be expressed by unnesting the nested set with respect to the outer result:

    SELECT   po.PONo, po.Cust_ref.CustNo, L.*
     FROM    PurchaseOrder_objtab po, TABLE (po.LineItemList_ntab) L
     WHERE   L.Stock_ref.StockNo = 1004 ;

The preceding query returns the result set as a flattened form (or First Normal Form). This type of query is useful when accessing Oracle collection columns from relational tools and APIs, such as ODBC. In the preceding unnesting example, only the rows of the `PurchaseOrder_objtab` object table that have any `LineItemList_ntab` rows are returned. To fetch all rows of the `PurchaseOrder_objtab` table, regardless of the presence of any rows in their corresponding `LineItemList_ntab`, then the (+) operator is required:

    SELECT   po.PONo, po.Cust_ref.CustNo, L.*
     FROM    PurchaseOrder_objtab po, TABLE (po.LineItemList_ntab) (+) L
     WHERE   L.Stock_ref.StockNo = 1004 ;

In Example A-38, the request requires querying the rows of all `LineItemList_ntab` nested tables of all `PurchaseOrder_objtab` rows. Again, unnesting is required:

Example A-38 Query Average Discount across all Line Items of all Purchase Orders

    SELECT AVG(L.DISCOUNT)
      FROM PurchaseOrder_objtab po, TABLE (po.LineItemList_ntab) L ;

#### Deleting

The following example has the same effect as the two deletions needed in the relational case shown in Example A-13. In Example A-39, Oracle deletes the entire purchase order object, including its line items, in a single SQL operation. In the relational case, line items for the purchase order must be deleted from the line items table, and the purchase order must be separately deleted from the purchase orders table.

Note: I f you are performing the SQL statements in this sample, do not execute the DELETE statement in Example A-39 because the purchase order is needed in the following examples.

Example A-39 Delete Purchase Order 1001 in an Object-Relational Model

    DELETE
     FROM   PurchaseOrder_objtab
     WHERE  PONo = 1001 ;

## Evolving Object Types

Even a completed, fully built application tends to be a work in progress. Sometimes requirements change, forcing a change an underlying object model or schema to adapt it to new circumstances, and sometimes there are ways to improve an object model so that it does a better job of what it was originally intended to do.

Suppose that, after living with our object-relational application for a while, we discover some ways that we could improve the design. In particular, suppose that we discover that users almost always want to see a history of purchases when they bring up the record for a customer. To do this with the present object model requires a join on the two tables `Customer_objtab` and `PurchaseOrder_objtab` that hold information about customers and purchase orders. We decide that a better design would be to provide access to data about related purchase orders directly from the customers table.

One way to do this is to change the `Customer_objtyp` so that information about a customer's purchase orders is included right in the object instance that represents that customer. In other words, we want to add an attribute for purchase order information to `Customer_objtyp`. To hold information about multiple purchase orders, the attribute must be a collection type—a nested table.

Adding an attribute is one of several ways that you can alter, or evolve, an object type. When you evolve a type, Oracle applies your changes to the type itself and to all its dependent schema objects, including subtypes of the type, other object types that have the altered type as an attribute, and tables and columns of the altered type.

To change `Customer_objtyp` to add an attribute for a nested table of purchase orders, several steps are needed:

1. Create a new type for a nested table of purchase orders

2. Alter `Customer_objtyp` to add a new attribute of the new type

3. In the `Customer_objtab` object table, name and scope the storage tables for the newly added nested tables

   - Upgrading the `Customer_objtab` object table for the new attribute actually adds two levels of nested tables, one inside the other, because a purchase order itself contains a nested table of line items.

   - Both the purchase orders nested table and the line items nested table need to be scoped so that they can contain primary key-based `REF`s. More on this in the next section.

Figure A-7 Nested Tables in the Customer Object Type

Description of the illustration adobj034.gif

When we are done with the preceding steps, information about customers and purchase orders will be more logically related in our model, and we will be able to query the customers table for all information about customers, purchase orders, and line items. We will also be able to insert a new purchase order for a new customer with a single `INSERT` statement on the customers table.

### Adding an Attribute to the Customer Type

Before we can add a nested table of purchase orders as an attribute of `Customer_objtyp`, we need to define a type for this sort of nested table. The following statement does this:

Example A-40 Create PurchaseOrderList_ntabtyp

    CREATE TYPE PurchaseOrderList_ntabtyp AS TABLE OF PurchaseOrder_objtyp;
    /

Now we can use an `ALTER` `TYPE` statement to add an attribute of this type to `Customer_objtyp`:

Example A-41 Alter Customer_objtyp

    ALTER TYPE Customer_objtyp
      ADD ATTRIBUTE (PurchaseOrderList_ntab PurchaseOrderList_ntabtyp)
      CASCADE;

If a type being altered has dependent types or tables, an `ALTER` `TYPE` statement on the type needs to specify either `CASCADE` or `INVALIDATE` to say how to apply the change to the dependents.

- `CASCADE` performs validation checks on the dependents before applying a type change. These checks confirm that the change does not entail doing something illegal, such as dropping an attribute that is being used as a partitioning key of a table. If a dependent fails validation, the type change aborts. On the other hand, if all dependents validate successfully, the system goes ahead with whatever changes to metadata and data are required to propagate the change to the type. These can include automatically adding and dropping columns, creating storage tables for nested tables, and so forth.

- The `INVALIDATE` option skips the preliminary validation checks and directly applies the type change to dependents. These are then validated the next time that they are accessed. Altering a type this way is saves the time required to do the validations, but if a dependent table cannot be validated later when someone tries to access it, its data cannot be accessed until the table is made to pass the validation.

We need to add scope for a `REF` column in each of the new nested tables of purchase orders and line items that are added to the `Customer_objtab` table. For convenience, first we rename the new tables from system-generated names to recognizable names. Then, using the names we have given them, we can alter the storage tables to add scope for their `REF` columns.

The reason we must do all this is that, in order for a column to store `REF`s to objects in a table that bases its object identifiers on the primary key, the column must be scoped to that table or have a referential constraint placed on it. Scoping a column to a particular table declares that all `REF`s in the column are `REF`s to objects in that table. This declaration is necessary because a primary key-based object identifier is guaranteed unique only in the context of the particular table: it may not be unique across all tables. If you try to insert a primary key-based `REF`, or user-defined `REF`, into an unscoped column, you will get an error similar to:

`cannot` `INSERT` `object` `view` `REF` `or` `user-defined` `REF`

Line items contain a `REF` to objects in table `Stock_objtab`, whose object identifier uses the table's primary key. This is why we had to add scope for the `REF` column in the storage table for the line items nested table in table `PurchaseOrder_objtab` after we created that table. Now we have to do it again for the new nested table of line items in table `Customer_objtab`.

We have to do the same again for the new nested table of purchase orders we are adding in table `Customer_objtab`: a purchase order references a customer in the table `Customer_objtab`, and object identifiers in this table are primary-key based as well.

Using the following statement, we determine the names of the system-generated tables so they can be renamed:

    SELECT table_name, parent_table_name, parent_table_column FROM user_nested_tables;

The output is similar to the following:

For convenience, rename the system-generated nested tables to appropriate names. For example, using the system-generated names in the previous sample output:

    ALTER TABLE "SYSNTQOFArJyBTHu6iOMMKU4wHw==" RENAME TO PO_List_nt;
    ALTER TABLE "SYSNTZqu6IQItR++UAtgz1rMB8A==" RENAME TO Items_List_nt;

The process of renaming the system-generated nested tables can also be done automatically with the following PL/SQL procedure:

    DECLARE
      nested_table_1 VARCHAR2(30);
      nested_table_2 VARCHAR2(30);
      cust_obj_table VARCHAR2(30) := 'CUSTOMER_OBJTAB';
    BEGIN
     EXECUTE IMMEDIATE ' SELECT table_name FROM user_nested_tables
        WHERE parent_table_name = :1 ' INTO nested_table_1 USING cust_obj_table;
     EXECUTE IMMEDIATE ' SELECT table_name FROM user_nested_tables
        WHERE parent_table_name = :1 ' INTO nested_table_2 USING nested_table_1;
     EXECUTE IMMEDIATE 'ALTER table "'|| nested_table_1 ||'" RENAME TO PO_List_nt';
     EXECUTE IMMEDIATE 'ALTER table "'|| nested_table_2 ||'" RENAME TO Items_List_nt';
    END;
    /

The new storage tables are named `PO_List_nt` and `Items_List_nt`. The following statements scope the `REF` columns in these tables to specific tables:

Example A-42 Add Scope for REF to Nested Tables

    ALTER TABLE PO_List_nt ADD (SCOPE FOR (Cust_Ref) IS Customer_objtab);
    ALTER TABLE Items_List_nt ADD (SCOPE FOR (Stock_ref) IS Stock_objtab);

There is just one more thing to do before inserting purchase orders for customers in `Customer_objtab`. An actual nested table of `PurchaseOrderList_ntabtyp` must be instantiated for each customer in the table.

When a column is added to a table for a new attribute, column values for existing rows are initialized to `NULL`. This means that each existing customer's nested table of purchase orders is atomically `NULL`—there is no actual nested table there, not even an empty one. Until we instantiate a nested table for each customer, attempts to insert purchase orders will get an error similar to:

`reference` `to` `NULL` `table` `value`

The following statement prepares the column to hold purchase orders by updating each row to contain an actual nested table instance:

Example A-43 Update Customer_objtab

    UPDATE Customer_objtab c
      SET c.PurchaseOrderList_ntab = PurchaseOrderList_ntabtyp();

In the preceding statement, `PurchaseOrderList_ntabtyp()` is a call to the nested table type's constructor method. This call, with no purchase orders specified, creates an empty nested table.

### Working with Multilevel Collections

At this point, we have evolved the type `Customer_objtyp` to add a nested table of purchase orders, and we have set up the table `Customer_objtab` so that it is ready to store purchase orders in the nested table. Now we are ready to insert purchase orders into `Customer_objtab`.

There are two purchase orders already in table `PurchaseOrder_objtab`. The following two statements copy these into `Customer_objtab`:

Example A-44 Insert Purchase Orders into Customer_objtab

    INSERT INTO TABLE (
      SELECT   c.PurchaseOrderList_ntab
        FROM   Customer_objtab c
        WHERE  c.CustNo = 1
      )
      SELECT VALUE(p)
        FROM PurchaseOrder_objtab p
        WHERE p.Cust_Ref.CustNo = 1;

    INSERT INTO TABLE (
      SELECT   c.PurchaseOrderList_ntab
        FROM   Customer_objtab c
        WHERE  c.CustNo = 2
      )
      SELECT VALUE(p)
        FROM PurchaseOrder_objtab p
        WHERE p.Cust_Ref.CustNo = 2;

#### Inserting into Nested Tables

Each of the preceding `INSERT` statements has two main parts: a `TABLE` expression that specifies the target table of the insert operation, and a `SELECT` that gets the data to be inserted. The `WHERE` clause in each part picks out the customer object to receive the purchase orders (in the `TABLE` expression) and the customer whose purchase orders are to be selected (in the subquery that gets the purchase orders).

The `WHERE` clause in the subquery uses dot notation to navigate to the `CustNo` attribute: `p.Cust_Ref.CustNo`. Note that a table alias `p` is required whenever you use dot notation. To omit it and say instead `Cust_Ref.CustNo` would produce an error.

Another thing to note about the dot notation in this `WHERE` clause is that we are able to navigate to the `CustNo` attribute of a customer right through the `Cust_Ref` `REF` attribute of a purchase order. SQL (though not PL/SQL) implicitly dereferences a `REF` used with the dot notation in this way.

The `TABLE` expression in the first part of the `INSERT` statement tells the system to treat the collection returned by the expression as a table. The expression is used here to select the nested table of purchase orders for a particular customer as the target of the insert.

In the second part of the `INSERT` statement, the `VALUE()` function returns selected rows as objects. In this case, each row is a purchase order object, complete with its own collection of line items. Purchase order rows are selected from one table of type `PurchaseOrder_objtyp` for insertion into another table of that type.

The preceding `INSERT` statements use the customer-reference attribute of `PurchaseOrder_objtyp` to identify the customer to whom each of the existing purchase orders belongs. However, now that all the old purchase orders are copied from the purchase orders table into the upgraded `Customer_objtab`, this customer-reference attribute of a purchase order is obsolete. Now purchase orders are stored right in the customer object itself.

The following `ALTER` `TYPE` statement evolves `PurchaseOrder_objtyp` to drop the customer-reference attribute. The statement also drops the `ShipToAddr_obj` attribute as redundant, assuming that the shipping address is always the same as the customer address.

Example A-45 Alter PurchaseOrder_objtyp

    ALTER TYPE PurchaseOrder_objtyp
        DROP ATTRIBUTE Cust_ref,
        DROP ATTRIBUTE ShipToAddr_obj
        CASCADE;

This time we were able to use the `CASCADE` option to let the system perform validations and make all necessary changes to dependent types and tables.

#### Inserting a New Purchase Order with Line Items

The previous `INSERT` example showed how to use the `VALUE()` function to select and insert into the nested table of purchase orders an existing purchase order object complete with its own nested table of line items. The following example shows how to insert a new purchase order that has not already been instantiated as a purchase order object. In this case, the purchase order's nested table of line items must be instantiated, as well as each line item object with its data. Line numbers are shown on the left for reference.

Example A-46 Insert into LineItemList_ntabtyp with VALUE()

    INSERT INTO TABLE (                                             /* Line 1  */
      SELECT c.PurchaseOrderList_ntab                               /* Line 2  */
        FROM Customer_objtab c                                      /* Line 3  */
        WHERE c.CustName = 'John Nike'                              /* Line 4  */
       )                                                            /* Line 5  */
      VALUES (1020, SYSDATE, SYSDATE + 1,                           /* Line 6  */
        LineItemList_ntabtyp(                                       /* Line 7  */
          LineItem_objtyp(1, MAKE_REF(Stock_objtab, 1004), 1, 0),   /* Line 8  */
          LineItem_objtyp(2, MAKE_REF(Stock_objtab, 1011), 3, 5),   /* Line 9  */
          LineItem_objtyp(3, MAKE_REF(Stock_objtab, 1535), 2, 10)   /* Line 10 */
          )                                                         /* Line 11 */
    );                                                              /* Line 12 */

Lines 1-5 use a `TABLE` expression to select the nested table to insert into—namely, the nested table of purchase orders for customer John Nike.

The `VALUES` clause (lines 6-12) contains a value for each attribute of the new purchase order, namely:

`PONo`
`OrderDate`
`ShipDate`
`LineItemList_ntab`

Line 6 of the `INSERT` statement specifies values for the three purchase order attributes `PONo`, `OrderDate`, and `ShipDate`.

Only attribute values are given; no purchase order constructor is specified. You do not need to explicitly specify a purchase order constructor to instantiate a purchase order instance in the nested table because the nested table is declared to be a nested table of purchase orders. If you omit a purchase order constructor, the system instantiates a purchase order automatically. You can, however, specify the constructor if you want to, in which case the `VALUES` clause will look like this:

    INSERT INTO TABLE (
      SELECT c.PurchaseOrderList_ntab
        FROM Customer_objtab c
        WHERE c.CustName = 'John Nike'
       )
    VALUES (
      PurchaseOrder_objtyp(1025, SYSDATE, SYSDATE + 1,
        LineItemList_ntabtyp(
          LineItem_objtyp(1, MAKE_REF(Stock_objtab, 1004), 1, 0),
          LineItem_objtyp(2, MAKE_REF(Stock_objtab, 1011), 3, 5),
          LineItem_objtyp(3, MAKE_REF(Stock_objtab, 1535), 2, 10)
         )
      )
    )

Lines 7-11 instantiate and supply data for a nested table of line items. The constructor method `LineItemList_ntabtyp(…)` creates an instance of such a nested table that contains three line items.

The line item constructor `LineItem_objtyp()` creates an object instance for each line item. Values for line item attributes are supplied as arguments to the constructor.

The `MAKE_REF` function creates a `REF` for the `Stock_ref` attribute of a line item. The arguments to `MAKE_REF` are the name of the stock table and the primary key value of the stock item there that we want to reference. We can use `MAKE_REF` here because object identifiers in the stock table are based on the primary key: if they were not, we would have to use the `REF` function in a subquery to get a `REF` to a row in the stock table.

#### Querying Multilevel Nested Tables

You can query a top-level nested table column by naming it in the `SELECT` list like any other top-level (as opposed to embedded) column or attribute, but the result is not very readable. For instance, the following query selects the nested table of purchase orders for John Nike:

Example A-47 Query Customer_objtab for Customer John Nike

    SELECT c.PurchaseOrderList_ntab
       FROM Customer_objtab c
       WHERE CustName = 'John Nike';

The query produces a result similar to the following:

    PURCHASEORDERLIST_NTAB(PONO, ORDERDATE, SHIPDATE, LINEITEMLIST_NTAB(LINEITEMNO,
    --------------------------------------------------------------------------------
    PURCHASEORDERLIST_NTABTYP(PURCHASEORDER_OBJTYP(2001, '25-SEP-01', '20-MAY-97', L
    INEITEMLIST_NTABTYP(LINEITEM_OBJTYP(10, 00004A038A00468ED552CE6A5803ACE034080020
    B8C8340000001426010001000100290000000000090600812A00078401FE0000000B03C20B050000
    ...

For humans, at least, you probably want to display the instance data in an unnested form and not to show the `REF`s at all. `TABLE` expressions—this time in the `FROM` clause of a query—can help you do this.

For example, the query in Example A-48 selects the PO number, order date, and shipdate for all purchase orders belonging to John Nike:

Example A-48 Query Customer_objtab Using TABLE Expression

    SELECT p.PONo, p.OrderDate, p.Shipdate
        FROM Customer_objtab c, TABLE(c.PurchaseOrderList_ntab) p
        WHERE c.CustName = 'John Nike';

`PONO ORDERDATE SHIPDATE`
`------- --------- ---------`
`2001 25-SEP-01 26-SEP-01`
`1020 25-SEP-01 26-SEP-01`

A `TABLE` expression takes a collection as an argument and can be used like a SQL table in SQL statements. In the preceding query, listing the nested table of purchase orders in a `TABLE` expression in the `FROM` clause enables us to select columns of the nested table just as if they were columns of an ordinary table. The columns are identified as belonging to the nested table by the table alias they use: `p`. As the example shows, a `TABLE` expression in the `FROM` clause can have its own table alias.

Inside the `TABLE` expression, the nested table is identified as a column of customer table `Customer_objtab` by the customer table's own table alias `c`. Note that the table `Customer_objtab` appears in the `FROM` clause before the `TABLE` expression that refers to it. This ability of a `TABLE` expressions to make use of a table alias that occurs to the left of it in the `FROM` clause is called left correlation. It enables you to daisy-chain tables and `TABLE` expressions—including `TABLE` expressions that make use of the table alias of another `TABLE` expression. In fact, this is how you are able to select columns of nested tables that are embedded in other nested tables.

Here, for example, is a query that selects information about all line items for PO number 1020:

Example A-49 Query Customer_objtab for Purchase Order 1020

    SELECT p.PONo, i.LineItemNo, i.Stock_ref.StockNo, i.Quantity, i.Discount
      FROM Customer_objtab c, TABLE(c.PurchaseOrderList_ntab) p,
        TABLE(p.LineItemList_ntab) i
      WHERE p.PONo = 1020;

`PONO LINEITEMNO STOCK_REF.STOCKNO QUANTITY DISCOUNT`
`----- ---------- ----------------- ---------- ----------`
`1020 1 1004 1 0`
`1020 2 1011 3 5`
`1020 3 1535 2 10`

The query uses two `TABLE` expressions, the second referring to the first. Line item information is selected from the inner nested table that belongs to purchase order number 1020 in the outer nested table.

Notice that no column from the customer table occurs in either the `SELECT` list or the `WHERE` clause. The customer table is listed in the `FROM` clause solely to provide a starting point from which to access the nested tables.

Here is a variation on the preceding query. This version shows that you can use the `*` wildcard to specify all columns of a `TABLE` expression collection:

    SELECT p.PONo, i.*
      FROM Customer_objtab c, TABLE(c.PurchaseOrderList_ntab) p,
        TABLE(p.LineItemList_ntab) i
      WHERE p.PONo = 1020;

### Type Inheritance and Substitutable Columns

Suppose that we deal with a lot of our larger, regular customers through an account manager. We would like to add a field for the ID of the account manager to the customer record for these customers.

Earlier, when we wanted to add an attribute for a nested table of purchase orders, we evolved the customer type itself. We could do that again to add an attribute for account manager ID, or we could create a subtype of the customer type and add the attribute only in the subtype. Which should we do?

To make this kind of decision, you need to consider whether the proposed new attribute can be meaningfully and usefully applied to all instances of the base type—to all customers, in other words—or only to an identifiable subclass of the base type.

All customers have purchase orders, so it was appropriate to alter the type itself to add an attribute for them. But not all customers have an account manager; in fact, it happens that only our corporate customers do. So, instead of evolving the customer type to add an attribute that will not be meaningful for customers in general, it makes more sense to create a new subtype for the special kind of customer that we have identified and to add the new attribute there.

#### Creating a Subtype

You can create a subtype under a base type only if the base type allows subtypes. Whether a type can be subtyped depends on the type's `FINAL` property. By default, new types are created as `FINAL`. This means that they are the last of the series and cannot have subtypes created under them. To create a type that can be subtyped, you must specify `NOT` `FINAL` in the `CREATE` `TYPE` statement as we did when we created the customer type.

You define a subtype by using a `CREATE` `TYPE` statement with the `UNDER` keyword. The following statement creates a new subtype `Corp_Customer_objtyp` under `Customer_objtyp`. The type is created as `NOT` `FINAL` so that it can have subtypes if we want to add them later.

Example A-50 Create Corp_Customer_objtyp

    CREATE TYPE Corp_Customer_objtyp UNDER Customer_objtyp
                (account_mgr_id     NUMBER(6) ) NOT FINAL;
    /

When you use a `CREATE` `TYPE` statement to create a new subtype, you list only the new attributes and methods that you are adding. The subtype inherits all existing attributes and methods from its base type, so these do not need to be specified. The new attributes and methods are added after the inherited ones. For example, the complete list of attributes for the new `Corp_Customer_objtyp` subtype looks like this:

`CustNo`
`CustName`
`Address_obj`
`Phonelist_var`
`PurchaseOrderList_ntab`
`Account_mgr_id`

By default, you can store instances of a subtype in any column or object table that is of any base type of the subtype. This ability to store subtype instances in a base type slot is called substitutability. Columns and tables are substitutable unless they have been explicitly declared to be `NOT` `SUBSTITUTABLE`. The system automatically adds new columns for subtype attributes and another, hidden column for the type ID of the instance stored in each row.

Actually, it is possible to create a subtype of a `FINAL` type, but first you must use an `ALTER` `TYPE` statement to evolve the type from a `FINAL` type to a `NOT` `FINAL` one. If you want existing columns and tables of the altered type to be able to store instances of new subtypes, specify the `CASCADE` option `CONVERT` `TO` `SUBSTITUTABLE` in the `ALTER` `TYPE` statement. See "Type Evolution".

#### Inserting Subtypes

If a column or object table is substitutable, you can insert into it not only instances of the declared type of the column or table but also instances of any subtype of the declared type. In the case of table `Customer_objtab`, this means that the table can be used to store information about all kinds of customers, both ordinary and corporate. However, there is one important difference in the way information is inserted for a subtype: you must explicitly specify the subtype's constructor. Use of the constructor is optional only for instances of the declared type of the column or table.

For example, the following statement inserts a new ordinary customer, William Kidd.

Example A-51 Insert Data for Ordinary Customer

    INSERT INTO Customer_objtab
      VALUES (
        3, 'William Kidd',
        Address_objtyp('43 Harbor Drive', 'Redwood Shores', 'CA', '95054'),
        PhoneList_vartyp('415-555-1212'),
        PurchaseOrderList_ntabtyp()
      );

The `VALUES` clause contains data for each `Customer_objtyp` attribute but omits the `Customer_objtyp` constructor. The constructor is optional here because the declared type of the table is `Customer_objtyp`. For the nested table attribute, the constructor `PurchaseOrderList_ntabtyp()` creates an empty nested table, but no data is specified for any purchase orders.

Here is a statement that inserts a new corporate customer in the same table. Note the use of the constructor `Corp_Customer_objtyp()` and the extra data value `531` for the account manager ID:

Example A-52 Insert Data for Corporate Customer

    INSERT INTO Customer_objtab
      VALUES (
        Corp_Customer_objtyp(   -- Subtype requires a constructor
          4, 'Edward Teach',
          Address_objtyp('65 Marina Blvd', 'San Francisco', 'CA', '94777'),
          PhoneList_vartyp('415-555-1212', '416-555-1212'),
          PurchaseOrderList_ntabtyp(), 531
        )
      );

The following statements insert a purchase order for each of the two new customers. Unlike the statements that insert the new customers, the two statements that insert purchase orders are structurally the same except for the number of line items in the purchase orders:

Example A-53 Insert Purchase Order for Ordinary Customer

    INSERT INTO TABLE (
      SELECT c.PurchaseOrderList_ntab
        FROM Customer_objtab c
        WHERE c.CustName = 'William Kidd'
       )
      VALUES (1021, SYSDATE, SYSDATE + 1,
        LineItemList_ntabtyp(
          LineItem_objtyp(1, MAKE_REF(Stock_objtab, 1535), 2, 10),
          LineItem_objtyp(2, MAKE_REF(Stock_objtab, 1534), 1, 0)
         )
       );

Example A-54 Insert Purchase Order for Corporate Customer

    INSERT INTO TABLE (
      SELECT c.PurchaseOrderList_ntab
        FROM Customer_objtab c
        WHERE c.CustName = 'Edward Teach'
       )
      VALUES (1022, SYSDATE, SYSDATE + 1,
        LineItemList_ntabtyp(
          LineItem_objtyp(1, MAKE_REF(Stock_objtab, 1011), 1, 0),
          LineItem_objtyp(2, MAKE_REF(Stock_objtab, 1004), 3, 0),
          LineItem_objtyp(3, MAKE_REF(Stock_objtab, 1534), 2, 0)
         )
       );

#### Querying Substitutable Columns

A substitutable column or table can contain data of several data types. This enables you, for example, to retrieve information about all kinds of customers with a single query of the customers table. But you can also retrieve information just about a particular kind of customer, or about a particular attribute of a particular kind of customer.

The following examples show some useful techniques for getting the information you want from a substitutable table or column.

The query in Example A-55 uses a `WHERE` clause that contains an `IS` `OF` predicate to filter out customers that are not some kind of corporate customer. In other words, the query returns all kinds of corporate customers but does not return instances of any other kind of customer:

Example A-55 Selecting All Corporate Customers and Their Subtypes

    SELECT c.*
      FROM Customer_objtab c
      WHERE VALUE(c) IS OF (Corp_Customer_objtyp);

The query in Example A-56 is similar to the preceding one except that it adds the `ONLY` keyword in the `IS` `OF` predicate to filter out any subtypes of `Corp_Customer_objtyp`. Rows are returned only for instances whose most specific type is `Corp_Customer_objtyp`.

Example A-56 Selecting All Corporate Customers with No Subtypes

    SELECT p.PONo
      FROM Customer_objtab c, TABLE(c.PurchaseOrderList_ntab) p
      WHERE VALUE(c) IS OF (ONLY Corp_Customer_objtyp);

The query in Example A-57 uses a `TABLE` expression to get purchase order numbers (from the nested table of purchase orders). Every kind of customer has this attribute, but the `WHERE` clause confines the search just to corporate customers:

Example A-57 Selecting PONo Just for Corporate Customers

    SELECT p.PONo
      FROM Customer_objtab c, TABLE(c.PurchaseOrderList_ntab) p
      WHERE VALUE(c) IS OF (Corp_Customer_objtyp);

The query in Example A-58 returns data for account manager ID. This is an attribute possessed only by the corporate customer subtype: the declared type of the table lacks it. In the query the `TREAT()` function is used to cause the system to try to regard or treat each customer as a corporate customer in order to access the subtype attribute `Account_mgr_id`:

Example A-58 Selecting a Subtype Attribute Using the TREAT Function

    SELECT CustName, TREAT(VALUE(c) AS Corp_Customer_objtyp).Account_mgr_id
      FROM Customer_objtab c
      WHERE VALUE(c) IS OF (ONLY Corp_Customer_objtyp);

`TREAT()` is necessary in Example A-58 because `Account_mgr_id` is not an attribute of the table's declared type `Customer_objtyp`. If you simply list the attribute in the `SELECT` list as if it were, a query like the one in Example A-59 will return the error `invalid` `column` `name` error. This is so even with a `WHERE` clause that excludes all but instances of `Corp_Customer_objtyp`. The `WHERE` clause is not enough here because it merely excludes rows from the result.

Example A-59 Selecting a Subtype Attribute Without the TREAT Function

    -- Following statement returns error, invalid column name for Account_mgr_id
    SELECT CustName, Account_mgr_id
      FROM Customer_objtab c
      WHERE VALUE(c) IS OF (ONLY Corp_Customer_objtyp);

Every substitutable column or object table has an associated hidden type-ID column that identifies the type of the instance in each row. You can look up the type ID of a type in the `USER_TYPES` catalog view.

The function `SYS_TYPEID()` returns the type ID of a particular instance. The query in Example A-60 uses `SYS_TYPEID()` and a join on the `USER_TYPES` catalog view to return the type name of each customer instance in the table `Customer_objtab`:

Example A-60 Discovering the Type of Each Instance

    SELECT c.CustName, u.TYPE_NAME
      FROM Customer_objtab c, USER_TYPES u
      WHERE SYS_TYPEID(VALUE(c)) = u.TYPEID;

`--------------------------------- ---------------------`
`Jean Nance CUSTOMER_OBJTYP`
`John Nike CUSTOMER_OBJTYP`
`William Kidd CUSTOMER_OBJTYP`
`Edward Teach CORP_CUSTOMER_OBJTYP`

For more information on `SYS_TYPEID()`, `VALUE()`, and `TREAT()`, see "Functions and Operators Useful with Objects".